package com.william.jdbcplus.core.crud.impl;

import com.william.jdbcplus.common.entity.Entity;
import com.william.jdbcplus.common.enums.FillType;
import com.william.jdbcplus.common.enums.IdType;
import com.william.jdbcplus.common.page.IPage;
import com.william.jdbcplus.common.page.Page;
import com.william.jdbcplus.core.crud.CrudConfig;
import com.william.jdbcplus.core.crud.IBaseCrud;
import com.william.jdbcplus.core.dialects.fill.FillFactory;
import com.william.jdbcplus.core.dialects.fill.KeyFactory;
import com.william.jdbcplus.core.dialects.pagination.PaginationFactory;
import com.william.jdbcplus.core.enums.SqlMethod;
import com.william.jdbcplus.core.metadata.TableColumnInfo;
import com.william.jdbcplus.core.metadata.TableHelperUtil;
import com.william.jdbcplus.core.metadata.TableInfo;
import com.william.jdbcplus.core.query.EntityWrap;
import com.william.jdbcplus.core.toolkit.JsonUtil;
import com.william.jdbcplus.core.toolkit.MapTransformUtils;
import com.william.jdbcplus.core.toolkit.Reflections;
import com.william.jdbcplus.core.toolkit.StringPool;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * 基本增册改查
 */
public class BaseCrud implements IBaseCrud {

    private <M extends Entity> void setPkVal(List<M> entityList, CrudConfig config) {
        if (entityList == null || entityList.size() == 0) {
            return;
        }

        M entity = entityList.get(0);
        Class<M> clazz = TableHelperUtil.getEntityClass(entity);
        TableInfo table = TableHelperUtil.getTableInfo(clazz);
        boolean keyIsString = false;
        if (table.getKeyType().equals(String.class)) {
            keyIsString = true;
        }

        if (table.getIdType() == IdType.INPUT) {
            return;
        }

        if (table.getIdType() != IdType.AUTO) {
            List<?> valueList = KeyFactory.getInstance(table.getIdType()).getValueList(clazz, table.getKeyProperty(), entityList.size(), config);
            for (int i = 0; i < entityList.size(); i++) {
                if (keyIsString) {
                    Reflections.invokeSetter(entityList.get(i), table.getKeyProperty(), valueList.get(i).toString());
                } else {
                    if (table.getKeyType() == Long.class) {
                        Reflections.invokeSetter(entityList.get(i), table.getKeyProperty(), valueList.get(i));
                    } else if (table.getKeyType() == BigInteger.class) {
                        Long value = (Long) valueList.get(i);
                        Reflections.invokeSetter(entityList.get(i), table.getKeyProperty(), BigInteger.valueOf(value));
                    } else if (table.getKeyType() == Integer.class) {
                        Long value = (Long) valueList.get(i);
                        Reflections.invokeSetter(entityList.get(i), table.getKeyProperty(), value.intValue());
                    } else {
                        Reflections.invokeSetter(entityList.get(i), table.getKeyProperty(), valueList.get(i));
                    }
                }
            }
        }
    }

    private <M extends Entity> void setFillVal(List<M> entityList, CrudConfig config) {
        if (entityList == null || entityList.size() == 0) {
            return;
        }

        M entity = entityList.get(0);
        Class<M> clazz = TableHelperUtil.getEntityClass(entity);
        TableInfo table = TableHelperUtil.getTableInfo(clazz);
        List<TableColumnInfo> columnList = table.getColumnList();
        for (TableColumnInfo column : columnList) {
            if (column.getFillType() == FillType.INPUT) {
                continue;
            }

            boolean fieldIsString = false;
            if (column.getPropertyType().equals(String.class)) {
                fieldIsString = true;
            }

            List<?> valueList = FillFactory.getInstance(column.getFillType()).getValueList(clazz, column.getProperty(), entityList.size(), config);
            for (int i = 0; i < entityList.size(); i++) {
                if (fieldIsString) {
                    Reflections.invokeSetter(entityList.get(i), column.getProperty(), valueList.get(i).toString());
                } else {
                    if (column.getPropertyType() == Long.class) {
                        Reflections.invokeSetter(entityList.get(i), column.getProperty(), valueList.get(i));
                    } else if (column.getPropertyType() == BigInteger.class) {
                        Long value = (Long) valueList.get(i);
                        Reflections.invokeSetter(entityList.get(i), column.getProperty(), BigInteger.valueOf(value));
                    } else if (column.getPropertyType() == Integer.class) {
                        Long value = (Long) valueList.get(i);
                        Reflections.invokeSetter(entityList.get(i), column.getProperty(), value.intValue());
                    } else {
                        Reflections.invokeSetter(entityList.get(i), column.getProperty(), valueList.get(i));
                    }
                }
            }
        }
    }

    private <M extends Entity> void setLogicVal(List<M> entityList) {
        if (entityList == null || entityList.size() == 0) {
            return;
        }

        M entity = entityList.get(0);
        Class<M> clazz = TableHelperUtil.getEntityClass(entity);
        TableInfo table = TableHelperUtil.getTableInfo(clazz);
        List<TableColumnInfo> columnList = table.getColumnList();
        for (TableColumnInfo column : columnList) {
            if (!column.isLogicDelete()) {
                continue;
            }
            for (int i = 0; i < entityList.size(); i++) {
                if (column.getPropertyType() == Long.class) {
                    Reflections.invokeSetter(entityList.get(i), column.getProperty(), Long.parseLong(column.getLogicNotDeleteValue()));
                } else if (column.getPropertyType() == BigInteger.class) {
                    Reflections.invokeSetter(entityList.get(i), column.getProperty(), BigInteger.valueOf(Long.parseLong(column.getLogicNotDeleteValue())));
                } else if (column.getPropertyType() == Integer.class) {
                    Reflections.invokeSetter(entityList.get(i), column.getProperty(), Integer.parseInt(column.getLogicNotDeleteValue()));
                } else if (column.getPropertyType() == Boolean.class) {
                    Reflections.invokeSetter(entityList.get(i), column.getProperty(), Boolean.parseBoolean(column.getLogicNotDeleteValue()));
                } else {
                    Reflections.invokeSetter(entityList.get(i), column.getProperty(), column.getLogicNotDeleteValue());
                }
            }
        }
    }

    @Override
    public <M extends Entity> int copyList(List<M> entityList, CrudConfig config) {
        if (entityList == null || entityList.size() == 0) {
            return 0;
        }

        int max = 1000;
        SqlMethod sqlMethod = SqlMethod.INSERT_LIST;
        M entity = entityList.get(0);
        Class<?> clazz = entity.getClass();
        String tableName = TableHelperUtil.getTableName(clazz);
        String columnInsert = TableHelperUtil.getColumnInsert(clazz);
        String valuesInsert = TableHelperUtil.getValuesInsert(clazz);
        String sql = String.format(sqlMethod.getSql(), tableName, columnInsert, valuesInsert);
        NamedParameterJdbcTemplate jdbcOpt = config.getNamedParameterJdbcTemplate();
        for (int fromIndex = 0; fromIndex < entityList.size(); fromIndex = fromIndex + max) {
            int toIndex = fromIndex + max;
            if (toIndex > entityList.size()) {
                toIndex = entityList.size();
            }

            List<M> rows = entityList.subList(fromIndex, toIndex);
            //对象转 MAP 列表
            List<Map<String, Object>> rowMap = TableHelperUtil.getEntityAttrMapList(rows);
            SqlParameterSource[] batchValues = SqlParameterSourceUtils.createBatch(rowMap);
            jdbcOpt.batchUpdate(sql, batchValues);
        }

        return entityList.size();
    }

    @Override
    public <M extends Entity> String insert(M entity, CrudConfig config) {
        SqlMethod sqlMethod = SqlMethod.INSERT_ONE;
        Class<M> clazz = TableHelperUtil.getEntityClass(entity);
        TableInfo table = TableHelperUtil.getTableInfo(clazz);
        String tableName = TableHelperUtil.getTableName(clazz);
        String columnInsert = TableHelperUtil.getColumnInsert(clazz);
        String valuesInsert = TableHelperUtil.getValuesInsert(clazz);
        String sql = String.format(sqlMethod.getSql(), tableName, columnInsert, valuesInsert);
        NamedParameterJdbcTemplate jdbcOpt = config.getNamedParameterJdbcTemplate();

        // 自动填充字段值
        setFillVal(Arrays.asList(entity), config);

        if (table.getIdType() == IdType.AUTO) {
            GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
            Map<String, Object> attrMap = TableHelperUtil.getEntityAttrMap(entity);
            SqlParameterSource paramSource = new MapSqlParameterSource(attrMap);
            jdbcOpt.update(sql, paramSource, keyHolder, new String[]{table.getKeyColumn()});
            Long autoId = keyHolder.getKey().longValue();
            Object id = JsonUtil.map(autoId, table.getKeyType());
            Reflections.invokeSetter(entity, table.getKeyProperty(), id);
            return autoId.toString();
        } else {
            setPkVal(Arrays.asList(entity), config);
            Map<String, Object> attrMap = TableHelperUtil.getEntityAttrMap(entity);
            SqlParameterSource paramSource = new MapSqlParameterSource(attrMap);
            jdbcOpt.update(sql, paramSource);
            return TableHelperUtil.getEntityIdVal(entity).toString();
        }
    }

    private static void setParameterValue(PreparedStatement ps, int paramIndex, Class<?> fieldType, Object inValue) throws SQLException {
        if (Short.class.equals(fieldType)) {
            if (inValue != null) {
                ps.setShort(paramIndex, (Short) inValue);
            } else {
                ps.setNull(paramIndex, Types.SMALLINT);
            }
        } else if (Integer.class.equals(fieldType)) {
            if (inValue != null) {
                ps.setInt(paramIndex, (Integer) inValue);
            } else {
                ps.setNull(paramIndex, Types.INTEGER);
            }
        } else if (Long.class.equals(fieldType)) {
            if (inValue != null) {
                ps.setLong(paramIndex, (Long) inValue);
            } else {
                ps.setNull(paramIndex, Types.BIGINT);
            }
        } else if (BigInteger.class.equals(fieldType)) {
            if (inValue != null) {
                ps.setLong(paramIndex, Long.parseLong(inValue.toString()));
            } else {
                ps.setNull(paramIndex, Types.BIGINT);
            }
        } else if (Boolean.class.equals(fieldType)) {
            if (inValue != null) {
                ps.setBoolean(paramIndex, (Boolean) inValue);
            } else {
                ps.setNull(paramIndex, Types.BOOLEAN);
            }
        } else if (Float.class.equals(fieldType)) {
            if (inValue != null) {
                ps.setFloat(paramIndex, (Float) inValue);
            } else {
                ps.setNull(paramIndex, Types.FLOAT);
            }
        } else if (Double.class.equals(fieldType)) {
            if (inValue != null) {
                ps.setDouble(paramIndex, (Double) inValue);
            } else {
                ps.setNull(paramIndex, Types.DOUBLE);
            }
        } else if (BigDecimal.class.equals(fieldType)) {
            if (inValue != null) {
                ps.setBigDecimal(paramIndex, (BigDecimal) inValue);
            } else {
                ps.setNull(paramIndex, Types.DECIMAL);
            }
        } else if (Byte.class.equals(fieldType)) {
            if (inValue != null) {
                ps.setByte(paramIndex, (Byte) inValue);
            } else {
                ps.setNull(paramIndex, Types.BIT);
            }
        } else if (Date.class.equals(fieldType)) {
            if (inValue != null) {
                ps.setDate(paramIndex, new java.sql.Date(((Date) inValue).getTime()));
            } else {
                ps.setNull(paramIndex, Types.DATE);
            }
        } else if (String.class.equals(fieldType)) {
            if (inValue != null) {
                ps.setString(paramIndex, inValue.toString());
            } else {
                ps.setNull(paramIndex, Types.VARCHAR);
            }
        } else {
            if (inValue != null) {
                ps.setString(paramIndex, inValue.toString());
            } else {
                ps.setNull(paramIndex, Types.VARCHAR);
            }
        }
    }

    @Override
    public <M extends Entity> int insertBatch(List<M> entityList, CrudConfig config) {
        if (entityList == null || entityList.size() == 0) {
            return 0;
        }

        int max = 10000;
        SqlMethod sqlMethod = SqlMethod.INSERT_LIST;
        M entity = entityList.get(0);
        Class<M> clazz = TableHelperUtil.getEntityClass(entity);
        String tableName = TableHelperUtil.getTableName(clazz);
        TableInfo table = TableHelperUtil.getTableInfo(clazz);
        String columnInsert = TableHelperUtil.getColumnInsert(clazz);
        String batchInsert = TableHelperUtil.getBatchInsert(clazz);
        String sql = String.format(sqlMethod.getSql(), tableName, columnInsert, batchInsert);
        setPkVal(entityList, config);
        setFillVal(entityList, config);
        setLogicVal(entityList);
        for (int fromIndex = 0; fromIndex < entityList.size(); fromIndex = fromIndex + max) {
            int toIndex = fromIndex + max;
            if (toIndex > entityList.size()) {
                toIndex = entityList.size();
            }

            List<M> rows = entityList.subList(fromIndex, toIndex);
            //对象转 MAP 列表
            List<Map<String, Object>> rowMap = TableHelperUtil.getEntityAttrMapList(rows);

            Connection connection = null;
            PreparedStatement ps = null;
            try {
                connection = config.getJdbcTemplate().getDataSource().getConnection();
                //这里必须设置为false，我们手动批量提交
                connection.setAutoCommit(false);
                //这里需要注意，SQL语句的格式必须是预处理的这种，就是values(?,?,...,?)，否则批处理不起作用
                ps = connection.prepareStatement(sql);
                for (Map<String, Object> map : rowMap) {
                    int parameterIndex = 0;
                    if (table.getIdType() != IdType.AUTO && table.getKeyColumn() != null) {
                        parameterIndex++;
                        setParameterValue(ps, parameterIndex, table.getKeyType(), map.get(table.getKeyProperty()));
                    }

                    for (TableColumnInfo column : table.getColumnList()) {
                        parameterIndex++;
                        setParameterValue(ps, parameterIndex, column.getPropertyType(), map.get(column.getProperty()));
                    }
                    //将要执行的SQL语句先添加进去，不执行
                    ps.addBatch();
                }
                //100W条SQL语句已经添加完成，执行这100W条命令并提交
                ps.executeBatch();
                connection.commit();
            } catch (Exception ex) {

            } finally {
                try {
                    if (null != ps) {
                        ps.close();
                    }
                    if (null != connection) {
                        connection.close();
                    }
                } catch (SQLException ex) {
                }
            }
        }

        return entityList.size();
    }

    @Override
    public <M extends Entity> int insertList(List<M> entityList, CrudConfig config) {
        if (entityList == null || entityList.size() == 0) {
            return 0;
        }

        int max = 1000;
        SqlMethod sqlMethod = SqlMethod.INSERT_LIST;
        M entity = entityList.get(0);
        Class<M> clazz = TableHelperUtil.getEntityClass(entity);
        String tableName = TableHelperUtil.getTableName(clazz);
        String columnInsert = TableHelperUtil.getColumnInsert(clazz);
        String valuesInsert = TableHelperUtil.getValuesInsert(clazz);
        String sql = String.format(sqlMethod.getSql(), tableName, columnInsert, valuesInsert);
        NamedParameterJdbcTemplate jdbcOpt = config.getNamedParameterJdbcTemplate();
        setPkVal(entityList, config);
        setFillVal(entityList, config);
        for (int fromIndex = 0; fromIndex < entityList.size(); fromIndex = fromIndex + max) {
            int toIndex = fromIndex + max;
            if (toIndex > entityList.size()) {
                toIndex = entityList.size();
            }

            List<M> rows = entityList.subList(fromIndex, toIndex);
            //对象转 MAP 列表
            List<Map<String, Object>> rowMap = TableHelperUtil.getEntityAttrMapList(rows);
            SqlParameterSource[] batchValues = SqlParameterSourceUtils.createBatch(rowMap);
            jdbcOpt.batchUpdate(sql, batchValues);
        }

        return entityList.size();
    }

    @Override
    public <M extends Entity> int deleteLogic(EntityWrap<M> wrap, CrudConfig config) {
        SqlMethod sqlMethod = SqlMethod.DELETE_LOGIC;
        String sqlLogicSet = getSqlLogicSet(wrap.getEntityClass());
        String sql = String.format(sqlMethod.getSql(), wrap.getTableName(), sqlLogicSet, wrap.getWhere());
        Map<String, Object> paramMap = wrap.getParamNameValuePairs();
        int result = config.getNamedParameterJdbcTemplate().update(sql, paramMap);
        return result;
    }

    @Override
    public <M extends Entity> int deleteReal(EntityWrap<M> wrap, CrudConfig config) {
        SqlMethod sqlMethod = SqlMethod.DELETE_REAL;
        String sql = String.format(sqlMethod.getSql(), wrap.getTableName(), wrap.getWhere());
        Map<String, Object> paramMap = wrap.getParamNameValuePairs();
        int result = config.getNamedParameterJdbcTemplate().update(sql, paramMap);
        return result;
    }

    @Override
    public <M extends Entity> int update(Map<String, Object> updateMap, EntityWrap<M> wrap, CrudConfig config) {
        SqlMethod sqlMethod = SqlMethod.UPDATE_BY_MAP;
        String sql = String.format(sqlMethod.getSql(), wrap.getTableName(), wrap.getUpdateSetByMap(updateMap), wrap.getWhere());
        Map<String, Object> paramMap = wrap.getParamNameValuePairs();
        int result = config.getNamedParameterJdbcTemplate().update(sql, paramMap);
        return result;
    }

    @Override
    public <M extends Entity> int selectCount(EntityWrap<M> wrap, CrudConfig config) {
        SqlMethod sqlMethod = SqlMethod.SELECT_COUNT;
        String sql = String.format(sqlMethod.getSql(), StringPool.ONE, wrap.getTableName(), wrap.getCountWhere());
        Map<String, Object> paramMap = wrap.getParamNameValuePairs();
        int result = config.getNamedParameterJdbcTemplate().queryForObject(sql, paramMap, Integer.class);
        return result;
    }

    @Override
    public <M extends Entity> List<M> selectList(EntityWrap<M> wrap, CrudConfig config) {
        SqlMethod sqlMethod = SqlMethod.SELECT_LIST;
        String sqlSelectAll = TableHelperUtil.getSelectAllColumnNoTable(wrap.getEntityClass());
        String sql = String.format(sqlMethod.getSql(), sqlSelectAll, wrap.getTableName(), wrap.getExpression().getWhereBehind());
        Map<String, Object> paramMap = wrap.getParamNameValuePairs();
        List<Map<String, Object>> listMap = config.getNamedParameterJdbcTemplate().queryForList(sql, paramMap);
        List<M> resultRows = MapTransformUtils.mapToEntityList(listMap, wrap.getEntityClass());
        return resultRows;
    }

    @Override
    public <M extends Entity> IPage<M> selectPage(IPage<?> page, EntityWrap<M> wrap, CrudConfig config) {
        SqlMethod sqlMethod = SqlMethod.SELECT_PAGE;
        int tatal = selectCount(wrap, config);
        String sqlSelectAll = TableHelperUtil.getSelectAllColumnNoTable(wrap.getEntityClass());
        String sqlSelect = String.format(sqlMethod.getSql(), sqlSelectAll, wrap.getTableName(), wrap.getExpression().getWhereBehind());
        String sqlPage = PaginationFactory.getPaginationSql(page, sqlSelect, config.getDbType());
        Map<String, Object> paramMap = wrap.getParamNameValuePairs();
        List<Map<String, Object>> listMap = config.getNamedParameterJdbcTemplate().queryForList(sqlPage, paramMap);
        List<M> resultRows = MapTransformUtils.mapToEntityList(listMap, wrap.getEntityClass());
        Page<M> result = new Page<>(page.getCurrent(), page.getSize());
        result.setRecords(resultRows);
        result.setTotal(tatal);
        return result;
    }

    /**
     * 获取逻辑删除字段值
     *
     * @return
     */
    public String getSqlLogicSet(Class<?> clazz) {
        TableInfo table = TableHelperUtil.getTableInfo(clazz);
        String sql = StringPool.EMPTY;
        if (table.isLogicDelete()) {
            TableColumnInfo field = table.getColumnList().stream().filter(TableColumnInfo::isLogicDelete).findFirst().get();
            if (field.getPropertyType() == String.class) {
                sql = field.getColumn() + StringPool.SPACE + StringPool.EQUALS + StringPool.SPACE + StringPool.SINGLE_QUOTE + field.getLogicDeleteValue() + StringPool.SINGLE_QUOTE;
            } else {
                sql = field.getColumn() + StringPool.SPACE + StringPool.EQUALS + StringPool.SPACE + field.getLogicDeleteValue();
            }
        }

        return sql;
    }

}

